using System;
using System.Threading;
using MySql.Data.MySqlClient;
using ConfigurationManager = Scenario.Modules.PCM.DataModel.ConfigurationManager;
using Scenario.Modules.PCM.DataModel.ConfigurationManager.Entity;
using Scenario.WorkflowEngine.DataModel.WorkflowRelevantData;

namespace Scenario.Modules.PCM.Database
{
	public class DBAdapterException : System.Exception 
	{
		public DBAdapterException(string sError) : base(sError)
		{
		}
	}

	public class PCMMappingTable : bOS.DataBase.bOSTable 
	{
		public string BusinessObject = string.Empty;
		public string PhisicalTable = string.Empty;
		public string OrderClause = string.Empty;
		public string WhereClause = string.Empty;
		public string SelectCommand = string.Empty;

		public void ReadFromDataBase(MySqlDataReader reader)
		{
			while ( reader.Read() )
			{
				this.BusinessObject = reader.GetString (0);
				this.PhisicalTable = reader.GetString (1);
				this.OrderClause = reader.GetString (2);
				this.WhereClause = reader.GetString (3);
				this.SelectCommand = reader.GetString (4);

				return;
			}
		}
	}

	
	public class PCMDataBase : bOS.DataBase.DataBaseManager 
	{
		private static Mutex mtx = new Mutex();
		private static PCMDataBase _instance = null;

		#region Singleton methods

		//Instance e Deinstance sono implementati qui solo a scopo di test.
		public static PCMDataBase Instance()
		{
			/*mtx.WaitOne();

			if ( _instance == null) 
			{
				_instance = new PCMDataBase("PCM", "ConnectionString");
			}

			mtx.ReleaseMutex();
			*/

			return _instance;
		}

		public static PCMDataBase Instance(string sConnectionString)
		{
			mtx.WaitOne();

			if ( _instance == null) 
			{
				_instance = new PCMDataBase(sConnectionString);
			}

			mtx.ReleaseMutex();

			return _instance;
		}

		public static void Deinstance()
		{
			lock(_instance)
			{
				_instance= null;
			}
		}

		#endregion

		public PCMDataBase(string sConnectionString) : base (sConnectionString)
		{
			XMLEntitiesHelper.LoadConfiguragion();
		}

		
		#region BusinessObject Mapping Table
		public PCMMappingTable GetPhisicalTable(string sBusinessObject)
		{
			bOS.DataBase.bOSTable mapTable = new PCMMappingTable();
			string sSQL = "select * from PCM_BO_Mapping where BusinessObject='" + 
				sBusinessObject + "'";

			this.ReadFromDataBase (sSQL, ref mapTable);

			return (PCMMappingTable)mapTable;
			
		}
		#endregion

		private XMLEntityAttribute GetEntityAttribute(string sEntityAttribute)
		{
			int pos = sEntityAttribute.IndexOf(".");

			string sAttributeName = sEntityAttribute.Substring(pos+1);
			string sEntityName =sEntityAttribute.Substring(0,pos);
				
			XMLEntity entity = null;
			XMLEntityAttribute entityAttribute = null;
		
			try
			{
				entity = XMLEntitiesHelper.XMLEntities().Get(sEntityName);
			}
			catch ( Scenario.Modules.PCM.DataModel.ConfigurationManager.Exception.EntityNotFound)
			{
				throw;
			}

			try
			{
				entityAttribute = entity.XMLEntityAttributes.Get(sAttributeName);
			}
			catch ( Scenario.Modules.PCM.DataModel.ConfigurationManager.Exception.EntityAttributeNotFound) 
			{
				throw;
			}

			return entityAttribute;
		}


		public XMLEntity GetEntity(string sEntityId)
		{
			return XMLEntitiesHelper.XMLEntities().GetById(sEntityId);
		}

		public XMLEntity GetEntityByName(string sEntityName)
		{
			return XMLEntitiesHelper.XMLEntities().Get(sEntityName);
		}
		
		#region retrieve SQL Commands
		private string GetBusinessObjectSelectCommand(int IdObject, string sEntityAttribute)
		{
			string sFilter = "Id=" + IdObject;
			PCMMappingTable mapTable = null;
			#region Retrieve Table or View for BusinessObject
			mapTable = this.GetPhisicalTable (sEntityAttribute);
			#endregion

			string sSQL = string.Empty;
			sSQL = " select *" ;
			sSQL+= " FROM " + mapTable.PhisicalTable;
			sSQL+= " WHERE " +sFilter;

			sSQL+= " Order by " + mapTable.OrderClause;

			return sSQL;
		}

		public string GetBusinessObjectSelectCommand(string sEntityAttribute, XMLAttributes filters, string sOneField)
		{
			string sFilter = string.Empty;
			string sOrder = string.Empty;
			string sTable = string.Empty;
			PCMMappingTable mapTable = null;
			#region Retrieve Table or View for BusinessObject

			mapTable = this.GetPhisicalTable (sEntityAttribute);
			#endregion

			#region Retrieve FilterCondition
			if ( filters != null )
			{
				for ( int i=0; i<filters.Count; i++)
				{
					XMLAttribute attribute = (XMLAttribute) filters[i];
					string sName = attribute.Name;
					string sValue = attribute.Value;

					string sField = string.Empty;

					string sFilterCondition= string.Empty;
					try
					{
						XMLEntityAttribute entityAttribute = this.GetEntityAttribute(attribute.Name);
						sFilterCondition = entityAttribute.FilterCondition;

						if ( entityAttribute.XMLDataType.Type == Scenario.WorkflowEngine.DataModel.WorkflowDataTypes.XMLDataType.NUMBER_DATATYPE )
						{
							sValue = sValue.Replace (",",".");
						}

						//modify by mfarina on 01/06/2006 for avoiding to apply this filter for SubEntities.
						//For SubEntities doesn't exist a fieldname equal to subentity name.
						if ( ( sFilterCondition == string.Empty ) && (entityAttribute.EntityType == XMLEntityAttribute.ENTITYTYPE_NOENTITY) )
						{
							int iPos = sName.IndexOf(".");
							sFilterCondition = sName.Substring(iPos+1) + "=" + sValue;
						}
							/*
							else if ( sFilterCondition == "NoFilter" )
							{
							}
							*/
						else if ( ( sValue == "" ) && (entityAttribute.EntityType == XMLEntityAttribute.ENTITYTYPE_NOENTITY) )
						{
							int iPos = sName.IndexOf(".");
							bool bFilter = true;

							try
							{
								bFilter = (sName.Substring (iPos+1, 6) != "Filter");
							}
							catch (System.Exception )
							{
								bFilter = true;
							}

							if ( bFilter )
							{
								if ( entityAttribute.DBField != string.Empty )
								{
									sFilterCondition = "(" + entityAttribute.DBField + " is null)";
								}
								else
								{
									sFilterCondition = "(" + sName.Substring(iPos+1) + " is null)";
								}
							}
						}
						else
						{
							sFilterCondition = sFilterCondition.Replace("[Value]", sValue);
						}

					}
					catch ( System.Exception )
					{
						int iPos = sName.IndexOf(".");
						sFilterCondition = sName.Substring(iPos+1) + "=" + sValue;
					}

					if ( sFilterCondition != "NoFilter" )
					{
						if ( sFilter.Length > 0 )
						{
							sFilter += " AND " + sFilterCondition;
						}
						else
						{
							sFilter = sFilterCondition;
						}
					}
				}
			}
			#endregion

			string sSQL = string.Empty;
			if ( sOneField == "" )
				sSQL = " select *" ;
			else
				sSQL = " select distinct(" + sOneField+ "), " + sOneField;

			sSQL+= " FROM " + mapTable.PhisicalTable;

			bool bFilterComposit = false;
			if ( mapTable.WhereClause.CompareTo ( string.Empty ) != 0 )
			{
				bFilterComposit =true;
				sSQL+=" WHERE " + mapTable.WhereClause;
			}

			if (sFilter.Length > 0)
			{
				if ( bFilterComposit ) 
				{
					sSQL+= " AND " + sFilter;
				}
				else
				{
					sSQL+= " WHERE " +sFilter;
				}
			}

			if ( sOneField == "")
			{
				sSQL+=" ORDER by " + mapTable.OrderClause;
			}
			else
			{
				sSQL+=" ORDER by " + sOneField;
			}

			sSQL+= ";";

			return sSQL;
		}

		private string GetBusinessObjectSelectCommand(string sEntityAttribute)
		{
			PCMMappingTable mapTable = null;
			#region Retrieve Table or View for BusinessObject
			mapTable = this.GetPhisicalTable (sEntityAttribute);
			#endregion
			
			string sSQL = mapTable.SelectCommand;
			return sSQL;
		}


		#endregion
		
		#region Records and Fields Retrieving Methods
		
		public BasicBusinessObjectCollection GetBusinessObjectRecords(string sEntityAttribute, XMLAttributes filters)
		{
			if (sEntityAttribute == null || sEntityAttribute.Length==0)
			{
				return null;
			}

			if (filters== null)
			{
				filters = new XMLAttributes();
			}

			string sSQL = this.GetBusinessObjectSelectCommand(sEntityAttribute,filters, "");

			if ( sSQL == string.Empty ) 
			{
				return null;
			}

			try
			{
				bOS.DataBase.bOSTable boRecords = new BasicBusinessObjectCollection();

				this.ReadFromDataBase ( sSQL, ref boRecords);

				return (BasicBusinessObjectCollection) boRecords;


			}
			catch (MySqlException err)
			{
				string sError = err.Message;
				sError+=sSQL;

				throw new DBAdapterException (sError);

			}
			
		}

		
		public bOS.DataBase.SelectionValuesCollection GetBusinessObjectAttributeList(string sEntityAttribute, XMLAttributes filters, string sDBAttribute)
		{
			if (sEntityAttribute == null || sEntityAttribute.Length==0)
			{
				return null;
			}

			if (filters== null)
			{
				filters = new XMLAttributes();
			}

			string sSQL = this.GetBusinessObjectSelectCommand(sEntityAttribute,filters, sDBAttribute);

			if ( sSQL == string.Empty ) 
			{
				return null;
			}

			try
			{
				bOS.DataBase.bOSTable boRecords = new bOS.DataBase.SelectionValuesCollection();

				this.ReadFromDataBase ( sSQL, ref boRecords);

				return (bOS.DataBase.SelectionValuesCollection) boRecords;

			}
			catch (MySqlException err)
			{
				string sError = err.Message;
				sError+=sSQL;

				throw new DBAdapterException (sError);

			}
		}


		public BasicBusinessObject GetBusinessObject(int iIdObject, string sEntityAttribute)
		{
			if (sEntityAttribute == null || sEntityAttribute.Length==0)
			{
				return null;
			}

			if (iIdObject<=0 )
			{
				return null;
			}

			string sSQL = this.GetBusinessObjectSelectCommand(iIdObject,sEntityAttribute);

			if ( sSQL == string.Empty ) 
			{
				return null;
			}

			try
			{
				bOS.DataBase.bOSTable boRecords = new BasicBusinessObjectCollection();

				this.ReadFromDataBase ( sSQL, ref boRecords);

				return ((BasicBusinessObjectCollection) boRecords)[0];


			}
			catch (MySqlException err)
			{
				string sError = err.Message;
				sError+=sSQL;

				throw new DBAdapterException (sError);

			}
			
		}


		public System.Data.DataView GetBusinessObjectDataView(string sEntity, string sAttribute, string sWhere)
		{
			if ( (sEntity == null) || (sEntity.Length==0) ||
			   (sAttribute == null) || (sAttribute.Length==0) )
			{
				return null;
			}

			XMLEntityAttribute xmlAttribute = XMLEntitiesHelper.XMLEntities().GetEntityAttribute (sEntity, sAttribute);

			if ( xmlAttribute == null)
			{
				return null;
			}
			
			string sSQL = this.GetBusinessObjectSelectCommand(xmlAttribute.ObjectData);

			if ( sSQL == string.Empty ) 
			{
				return null;
			}

			if ( ( sWhere.CompareTo(string.Empty) != 0 ) && (sSQL.IndexOf ("where") > 0 ) )
			{
				sWhere="AND " + sWhere;
			}

			sSQL = sSQL.Replace ("#WHERE", sWhere);

			return this.GetDataView(sSQL);
		}

		#endregion

	}
}
